package com.plug.controller;

import com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException;
import com.plug.common.consts.Code;
import com.plug.common.model.ExceptionResult;
import com.plug.common.model.JsonResult;
import com.plug.common.quartz.QuartzManager;
import com.plug.common.quartz.job.QuartzJob;
import com.plug.controller.util.AbstractController;
import com.plug.entity.PlugContrast;
import com.plug.entity.PlugDatasource;
import com.plug.entity.PlugTaskState;
import com.plug.service.IPlugContrastService;
import com.plug.service.IPlugDatasourceService;
import com.plug.service.IPlugTaskStateService;
import com.plug.util.StringUtils;
import com.sun.javafx.image.IntPixelGetter;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;
import org.quartz.SchedulerException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;

import java.sql.SQLException;
import java.util.*;

/**
 * Created by slaye on 2016/12/21.
 */
@Controller
@RequestMapping("/datasource")
public class DataSourceController extends AbstractController {

    private Logger logger = LoggerFactory.getLogger(getClass());

    @Autowired
    private IPlugDatasourceService plugDatasourceService;

    @Autowired
    private IPlugContrastService plugContrastService;

    @Autowired
    private IPlugTaskStateService plugTaskStateService;

    /**
     * 新增或保存数据源
     * @param json
     * @return
     */
    @RequestMapping(value = "/save", method = RequestMethod.POST)
    @ResponseBody
    public ResponseEntity<Object> save(String json){
        try{
            JSONObject jsonObject = new JSONObject(json);
            PlugDatasource p = new PlugDatasource();//组装添加或修改对象
            if(!jsonObject.isNull("id") && !StringUtils.isNull(jsonObject.getString("id"))){p.setId(Long.valueOf(jsonObject.getString("id")));}
            if(!jsonObject.isNull("dsName") && !StringUtils.isNull(jsonObject.getString("dsName"))){p.setDsName(jsonObject.getString("dsName"));} else {throw new Exception("数据库描述名称不能为空，请填写完整！");}
            if(!jsonObject.isNull("dsType")){p.setDsType(jsonObject.getString("dsType"));}
            if(!jsonObject.isNull("dsDriver") && !StringUtils.isNull(jsonObject.getString("dsDriver"))){p.setDsDriver(jsonObject.getString("dsDriver"));} else {throw new Exception("数据库驱动不能为空，请填写完整！");}
            if(!jsonObject.isNull("url") && !StringUtils.isNull(jsonObject.getString("url"))){
                if(jsonObject.getString("dsType").equals("ORACLE")){
                    p.setDsUrl("jdbc:oracle:thin:@" + jsonObject.getString("url") + ":" + jsonObject.getString("dsDesc"));
                } else {
                    p.setDsUrl("jdbc:mysql://" + jsonObject.getString("url") + "/" + jsonObject.getString("dsDesc") + "?useUnicode=true&characterEncoding=utf8");
                }
            } else {throw new Exception("IP及端口号格式不正确，请按localhost:3306格式填写！");}
            if(!jsonObject.isNull("dsUsername") && !StringUtils.isNull(jsonObject.getString("dsUsername"))){p.setDsUsername(jsonObject.getString("dsUsername"));} else {throw new Exception("数据库用户名不能为空，请填写完整！");}
            if(!jsonObject.isNull("dsPassword") && !StringUtils.isNull(jsonObject.getString("dsPassword"))){p.setDsPassword(jsonObject.getString("dsPassword"));} else {throw new Exception("数据库密码不能为空，请填写完整！");}
            if(!jsonObject.isNull("dsDesc") && !StringUtils.isNull(jsonObject.getString("dsDesc"))){p.setDsDesc(jsonObject.getString("dsDesc"));} else {throw new Exception("数据库名不能为空，请填写完整！");}
            p.setCreatetime(new Date());


            int result = plugDatasourceService.save(p);
            JsonResult jsonResult = new JsonResult();
            if(result > 0) {
                jsonResult.setCode(Code.SUCCESS);
                jsonResult.setMessage("保存成功！");
            } else {
                jsonResult.setCode(Code.FAILED);
                jsonResult.setMessage("服务器繁忙，请稍后再试！");
            }

            return new ResponseEntity<Object>(jsonResult, HttpStatus.OK);
        } catch (Exception e){
            return new ResponseEntity<Object>(new ExceptionResult(e), HttpStatus.OK);
        }
    }




    /**
     *批量新增或保存数据源
     * @param jsonObj
     * @return
     */
    @RequestMapping(value = "/saveData", method = RequestMethod.POST)
    @ResponseBody
    public Map<String, Object> saveData(String jsonObj) throws JSONException {
        Map<String, Object> resultMap = new HashMap<>();
        JSONArray jsonArray = new JSONArray(jsonObj);
        int result = 0;
        for (int i = 0; i < jsonArray.length(); i++) {
            JSONObject jo = jsonArray.getJSONObject(i);
            PlugDatasource p = new PlugDatasource();
            if(!jo.isNull("id") && !StringUtils.isNull(jo.getString("id"))){p.setId(Long.valueOf(jo.getString("id")));}
            if(!jo.isNull("dsName") && !StringUtils.isNull(jo.getString("dsName"))){p.setDsName(jo.getString("dsName"));} else {continue;}
            if(!jo.isNull("dsType")){p.setDsType(jo.getString("dsType"));}
            if(!jo.isNull("dsDriver") && !StringUtils.isNull(jo.getString("dsDriver"))){p.setDsDriver(jo.getString("dsDriver"));} else {continue;}
            if(!jo.isNull("url") && !StringUtils.isNull(jo.getString("url"))){
                if(jo.getString("dsType").equals("ORACLE")){
                    p.setDsUrl("jdbc:oracle:thin:@" + jo.getString("url") + ":" + jo.getString("dsDesc"));
                } else {
                    p.setDsUrl("jdbc:mysql://" + jo.getString("url") + "/" + jo.getString("dsDesc") + "?useUnicode=true&characterEncoding=utf8");
                }
            } else {continue;}
            if(!jo.isNull("dsUsername") && !StringUtils.isNull(jo.getString("dsUsername"))){p.setDsUsername(jo.getString("dsUsername"));} else {continue;}
            if(!jo.isNull("dsPassword") && !StringUtils.isNull(jo.getString("dsPassword"))){p.setDsPassword(jo.getString("dsPassword"));} else {continue;}
            if(!jo.isNull("dsDesc") && !StringUtils.isNull(jo.getString("dsDesc"))){p.setDsDesc(jo.getString("dsDesc"));} else {continue;}
            p.setCreatetime(new Date());
            result += plugDatasourceService.save(p);
        }

        if(result > 0) {
            resultMap.put("code", 0);
            resultMap.put("msg", "更新"+result+"条数据！");
        } else {
            resultMap.put("code", -1);
            resultMap.put("msg", "更新失败！");
        }
        return resultMap;
    }




    /**
     * 分页查询数据
     * @param name
     * @param page
     * @param rows
     * @return
     */
    @RequestMapping(value = "/pages",method = RequestMethod.POST)
    @ResponseBody
    public Map<String, Object> getDataSourceList(@RequestParam("name")String name,
                                                 @RequestParam("page")int page,
                                                 @RequestParam("rows")int rows) {
        Map<String, Object> resultMap = new HashMap<>();
        name = name == null ? "%%" : "%" + name +"%";
        logger.debug("name------------{}--------------", name);
        rows = rows == 0 ? 25 : rows;
        int total = plugDatasourceService.findTotal(name);
        List<PlugDatasource> list = plugDatasourceService.pages(name, (page - 1) * rows, rows);
        resultMap.put("total", total);
        resultMap.put("rows", list);
        return resultMap;
    }




    /**
     * 删除数据源
     * @param id
     * @return
     */
    @RequestMapping(value = "/delete/{id}", method = RequestMethod.DELETE)
    @ResponseBody
    public Map<String, Object> delete(@PathVariable("id") Long id){
        Map<String, Object> resultMap = new HashMap<>();
        if(plugContrastService.findContrastByDsID(id) > 0) {
            resultMap.put("code", -1);
            resultMap.put("msg", "该条数据已被关联，请先删除关联数据后在删除！");
        } else {
            int result = plugDatasourceService.delete(id);
            if(result > 0) {
                resultMap.put("code", 0);
                resultMap.put("msg", "删除成功！");
            } else {
                resultMap.put("code", -1);
                resultMap.put("msg", "删除失败！");
            }
        }
        return resultMap;
    }




    /**
     * 分页查询数据源配置信息
     * @param page
     * @param rows
     * @return
     */
    @RequestMapping(value = "/showInfo",method = RequestMethod.POST)
    @ResponseBody
    public Map<String, Object>  pageDataSourecInfos(@RequestParam("page")int page,@RequestParam("rows")int rows){
       //int pageSize = rows != 2 ? 2 : rows;
       Map<String, Object> pageMap=new HashMap<>();
       int number=plugContrastService.findAll().size();//获取总记录数
       List<Map<String, Object>> listOb = plugContrastService.dataSourecInfo((page - 1) * rows,rows);
        logger.debug("rows页大小:"+rows);
        pageMap.put("total",number); //总记录
        pageMap.put("rows",listOb);//当前条
        logger.debug("listOb分页后:{}",listOb);
        return pageMap ;
    }




    /**
     * 删除数据源
     * @param taskId
     * @return
     */
    @RequestMapping(value="/deleteContrast",method = RequestMethod.POST)
    @ResponseBody
    public Map<String, Object> deleteContrastSourec(@RequestParam("taskId") String[] taskId,@RequestParam("names") String[] names){

        Map<String, Object> msgMap=new HashMap<>();
        List<PlugContrast> plugContrastList=new ArrayList<>();
        if(taskId!=null){

            for(int i=0;i<taskId.length;i++){
                PlugContrast plugContrast=new PlugContrast();
                plugContrast.setTaskId(taskId[i]);
                plugContrastList.add(plugContrast);

                try {
                    QuartzManager.deleteJob(taskId[i]); //删除任务
                } catch (SchedulerException e) {
                    e.printStackTrace();
                    msgMap.put("code", 2);
                    msgMap.put("msg", "任务删除失败！");
                }
            }

            List<String> nameList=new ArrayList<>();
            for (int i = 0; i < names.length; i++) {
                nameList.add("备份任务"+names[i]);
            }

            if(plugContrastList.size()>0 && names.length>0){

                int num=plugContrastService.deleteByPrimaryKey(plugContrastList); //删除备份记录
                List<Integer> idList=plugTaskStateService.selectIdByname(nameList);  //根据任务名称查询 id

                if(num==plugContrastList.size()){
                    if(idList!=null && idList.size()>0){
                        plugTaskStateService.deleteBatch(idList);  //删除任务日子状态
                    }
                    msgMap.put("code",0);
                    msgMap.put("msg","删除成功！");
                }else{
                    msgMap.put("code",-1);
                    msgMap.put("msg", "删除失败！");
                    return msgMap;
                }
            }
        }else{
            msgMap.put("code",00);
            msgMap.put("msg", "未得到数据请检查!");
        }
        return msgMap;
    }

    /**
     * 获取数据源配置信息
     * @return
     */
    @RequestMapping(value="/findallDs",method = RequestMethod.POST)
    @ResponseBody
    public Map<String, Object> findallDataSourec(){

        Map<String, Object> map=new HashMap<>();
        List<Map<String, Object>> listMap=new ArrayList<>();
        List<PlugDatasource> list =plugDatasourceService.findall();  //获取所有的数据源信息
        logger.debug("数据源：PlugDatasourcelist{}",list);

        for (PlugDatasource d:list){
            Map<String, Object> maps=new HashMap<>();
            maps.put("text",d.getDsName());
            maps.put("value",d.getId());
            listMap.add(maps);
        }
        map.put("data",listMap);
        return map;
    }




    /**
     * 新增ContrastSourec信息
     * @param record
     * @return
     */
    @RequestMapping(value = "/addContrastSourec",method =RequestMethod.POST)
    @ResponseBody
    public Map<String,Object>  addContrastSourec(PlugContrast record){

        Map<String,Object> msgMap=new HashMap();
        List<PlugContrast> plugContrastlist=new ArrayList<>(); //存储要新增的PlugContrast

        if(record!=null){

            String[] srcTabs=record.getSrcTab().split(",");
            String[] tgTabs=record.getTgtTab().split(",");
            String[] primaryKey=record.getPrimaryKey().split(",");

            for (int j = 0; j <srcTabs.length; j++) {
                String uuid = UUID.randomUUID().toString().toUpperCase();
                PlugContrast records=new PlugContrast(); //组装对象
                records.setCreatetime(new Date());
                records.setSrcDsId(record.getSrcDsId());
                records.setSrcTab(srcTabs[j]);
                records.setTgtDsId(record.getTgtDsId());
                records.setTgtTab(tgTabs[j]);
                records.setTaskId(uuid);
                records.setStatus(0);
                records.setSqlWhere(record.getSqlWhere());

                if(StringUtils.isNull(record.getName())){
                    records.setName(":"+record.getSrcDsName()+"("+srcTabs[j]+")->"+record.getTgtDsName()+"("+tgTabs[j]+")");
                }else{
                    records.setName(record.getName()+":"+record.getSrcDsName()+"("+srcTabs[j]+")->"+record.getTgtDsName()+"("+tgTabs[j]+")");
                }

                if(primaryKey.length==1){
                    records.setPrimaryKey(record.getPrimaryKey());
                }else{
                    records.setPrimaryKey(primaryKey[j]);
                }

                if(record.getTimer()==null){
                    records.setTimer(30);
                }else{
                    records.setTimer(record.getTimer());

                }
                plugContrastlist.add(records);
            }

            logger.debug("----------------------：plugContrastlist{}", plugContrastlist.size());

            if( plugContrastlist!=null && plugContrastlist.size()>0){
                //添加任务
                for(PlugContrast c:plugContrastlist){
                    int count= plugContrastService.insert(c);

                    if(count==1){
                        msgMap.put("code",0);
                        msgMap.put("msg","新增成功！");
                        try {
                            QuartzManager.addJob(c.getTaskId(), c.getTaskId(), QuartzJob.class, c.getTimer());// 正式添加给我任务调度
                        } catch (SchedulerException e) {
                            msgMap.put("code",2);
                            msgMap.put("msg","任务启动失败！");
                            return msgMap;
                            //e.printStackTrace();
                        }

                    }else{
                        msgMap.put("code",-1);
                        msgMap.put("msg","可能存在相同任务，请核实！");
                    }
                }
            }
        }else{
            msgMap.put("code",00);
            msgMap.put("msg","未得到数据请检查!");
        }
        return msgMap;
    }



    /**
     * 修改ContrastSourec信息
     * @param record
     * @return
     */
    @RequestMapping(value ="/upContrastSourec",method =RequestMethod.POST)
    @ResponseBody
    public Map<String,Object> upContrastSourec(PlugContrast record,@RequestParam("taskName") String taskName){

        logger.debug("修改的数据:{}", record);
        Map<String, Object> msgMap=new HashMap<>();
        if(record!=null) {

            //设置创建时间
            record.setCreatetime(new Date());
            //设置任务名称
            record.setName(record.getName() + ":" + record.getSrcDsName() + "(" + record.getSrcTab() + ")->" + record.getTgtDsName() + "(" + record.getTgtTab() + ")");
            //如果时间为空就默认30秒
            if (record.getTimer() == null) {
                record.setTimer(30);
            }

            //修改备份任务
            int num = plugContrastService.updateByPrimaryKeySelective(record);

            //根据任务名称查询 id
            PlugTaskState taskState = plugTaskStateService.selectByName("备份任务"+taskName);
            List<Integer> idList = new ArrayList<>();
            //添加到idList
            if (taskState != null) {
                idList.add(Integer.parseInt(taskState.getId().toString()));
            }


            if (num >= 0) {
                try {
                    if (idList != null && idList.size() > 0) {
                        //删除任务日志
                        plugTaskStateService.deleteBatch(idList);
                    }
                    //先删除任务
                    QuartzManager.deleteJob(record.getTaskId());
                    // 添加给我任务调度
                    QuartzManager.addJob(record.getTaskId(), record.getTaskId(), QuartzJob.class, record.getTimer());
                } catch (SchedulerException e) {
                    msgMap.put("code", 2);
                    msgMap.put("msg", "任务启动失败！");
                    return msgMap;
                   // e.printStackTrace();
                }
                //int count=plugContrastService.selectContrast(record);  //查询是否存在
                msgMap.put("code", 0);
                msgMap.put("msg", "修改成功！");
            } else {
                msgMap.put("code", -1);
                msgMap.put("msg", "可能存在相同任务，请核实！");
            }

        }else{
            msgMap.put("code", 00);
            msgMap.put("msg", "未得到数据请检查！");
        }

        return msgMap;
    }




    /**
     * 根据数据库名称获取属下所有表名
     * @param id
     * @return
     */
    @RequestMapping(value = "/findDataBaseByName", method =RequestMethod.POST)
    @ResponseBody
    public Map<String,Object> findDataBaseByName(@RequestParam("id") Long id){

        Map<String,Object> map=new HashMap<>();
        List<Map<String, Object>> listMap=new ArrayList<>();
        PlugDatasource plugDatasource=plugDatasourceService.findByID(id);

        try {

            List<Map<String, Object>> listTableName= plugDatasourceService.findDataBaseByName(plugDatasource);
            logger.debug("根据数据库id获取属下所有表名：listTableName{}",listTableName);
            if(listTableName!=null && listTableName.size()>0){
                for (int i=0;i<listTableName.size();i++){

                    String tableName=listTableName.get(i).get("TABLE_NAME").toString();
                    Map<String,Object> comboxMap=new HashMap<>();
                    comboxMap.put("text",tableName); //table_name是select table_name from #{dbName}
                    comboxMap.put("value",i+1);
                    listMap.add(comboxMap);
                }
            }else{
                map.put("code",0);
            }

        } catch (NullPointerException npe) {
            map.put("code",-1);
            map.put("msg","数据库连接失败！");
            return map;
           // npe.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        map.put("data",listMap);
        return map;
    }

    /**
     * 根据数据库名及获取的备份表检查当前库的表是否对应
     * @param id
     * @param srcTabs
     * @return
     */
/*  @RequestMapping(value = "/checkDataBaseByName", method =RequestMethod.POST)
    @ResponseBody
    public Map<String,Object> checkDataBaseByName(@RequestParam("id") long id,@RequestParam("srcTabs") String srcTabs){


        Map<String,Object> map=new HashMap<>();
        List<Map<String, Object>> listMap=new ArrayList<>();
        PlugDatasource plugDatasource=plugDatasourceService.findByID(id);
        List<Map<String, Object>> listTableName=plugDatasourceService.findDataBaseByName(plugDatasource);

        logger.debug("根据数据库名称获取属下所有表名：listTableName{}",listTableName);
        int isNum=srcTabs.indexOf(",");
        int contrastNum=0;
        String[] arrTab=srcTabs.split(",");
        if(listTableName.size()>0){
            for (int i=0;i<listTableName.size();i++){

                String tableName=listTableName.get(i).get("TABLE_NAME").toString();
                Map<String,Object> comboxMap=new HashMap<>();
                comboxMap.put("text",tableName); //table_name是select table_name from #{dbName}
                comboxMap.put("value",i+1);
                listMap.add(comboxMap);

                //查看是否存在
                for (int j = 0; j <arrTab.length ; j++) {
                    if(arrTab[j].equals(tableName)){
                        contrastNum=contrastNum+1;
                    }
                }

            }
            map.put("data",listMap);
            if(isNum!=-1){
                //如果以上表名对比记录与得到的表名记录相同
                if(contrastNum==arrTab.length){
                    Map<String,Object> comboxMap=new HashMap<>();
                    comboxMap.put("text",srcTabs); //table_name是select table_name from #{dbName}
                    comboxMap.put("value",-1);
                    listMap.add(comboxMap);
                    map.put("code",1);
                    map.put("msg","多选时默认和数据表一致");
                }else{
                    map.put("code",-1);
                    map.put("msg","备份库无法对应源库中的表");
                }
                map.put("data",listMap);
            }else{
                map.put("code",0);
            }
        }else{
            map.put("msg","无数据！");
        }

        return map;
    }*/

}
